CREATE VIEW [dbo].[vRelationship]
AS
    SELECT
        Relationship.ID AS ID,
        Relationship.RELATION_TYPE AS Relationship,
        Relationship.TARGET_ID AS RelatedToID,
        Relationship.TARGET_NAME AS RelatedToName,
        Relationship.TITLE AS Title,
        Relationship.FUNCTIONAL_TITLE AS FunctionalTitle,
        Relationship.STATUS AS Status,
        Relationship.EFFECTIVE_DATE AS BeginDate,
        Relationship.THRU_DATE AS EndDate,
        Relationship.NOTE AS Note,
        Relationship.GROUP_CODE AS GroupCode
    FROM Relationship
    UNION ALL
    SELECT
        Relationship.TARGET_ID AS ID,
        Relationship.TARGET_RELATION_TYPE AS Relationship,
        Relationship.ID AS RelatedToID,
        '' AS RelatedToName,
        '' AS Title,
        '' AS FunctionalTitle,
        Relationship.STATUS AS Status,
        Relationship.EFFECTIVE_DATE AS BeginDate,
        Relationship.THRU_DATE AS EndDate,
        Relationship.NOTE AS Note,
        Relationship.GROUP_CODE AS GroupCode
    FROM Relationship
    WHERE Relationship.TARGET_ID <> ''

GO